package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import persistence.DBSession;

/**
 * @author: Humberto Rocha Loureiro (humbertorocha@gmail.com)
 * @modify: 
 */

public class IndicatoriEngineDAO { 

	private Connection conn = null;
	private ResultSet rs = null;
	private Statement stat = null;

	public List<IndicatorVO> listIndicatorTrends(String date) {
		List<IndicatorVO> result = new ArrayList<IndicatorVO>();
		conn = DBSession.getIstance().getConnection();
		PreparedStatement pstat = null;
		
		try {
			String sql = " select t1.idstock, t2.volume, t2.close, " +
						 "        (close / MaxMinIndex(t1.idstock, t1.date, 'MI05') * 100) - 100 as MI05_perc, " +
						 " 		  (close / MaxMinIndex(t1.idstock, t1.date, 'MI04') * 100) - 100 as MI04_perc, " +
						 "		   MaxMinIndex(t1.idstock, t1.date, 'MI05') as MI05, " +
						 "		   MaxMinIndex(t1.idstock, t1.date, 'MI04') as MI04, " +
						 "		   (SELECT MediaMinima(t1.idstock,t1.date)) as volat_min, " +
						 "		   (SELECT MediaMinima(t1.idstock,t1.date))*2 as volat_max, " +
						 "		   ((t2.high / t2.low) * 100) -100 as percHL, " +
						 "		    t2.close / ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1) as buy_1, " +
//						 "		   (t2.close / ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1)) / 1.05 as stop_1, " +
//						 "		   (t2.close / ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1)) as stop_1, " +
//						 "		   (t2.close / ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1)) * ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1) as limit_1, " +

						 "			t2.close / ((((SELECT MediaMinima(t1.idstock,t1.date)*4)/2)/100)+1) as buy_2, " +
//						 "			(t2.close / ((((SELECT MediaMinima(t1.idstock,t1.date)*4)/2)/100)+1)) / 1.05 as stop_2, " +
//						 "			(t2.close / ((((SELECT MediaMinima(t1.idstock,t1.date)*4)/2)/100)+1)) * ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1) as limit_2, " +
						 "			 t1.buy, t1.sell, t1.idscript " +
						 "	from analyzed t1, quote t2, stocks t3 " +
						 "	where " +
						 "		t1.date = ? " +
						 "	and t1.date = t2.date " +
						 "	and t1.idstock = t2.stock " +
						 "	and t3.stock = t2.stock " +
						 "	and t3.active = 1 " +
						 "	and t2.volume > 10000 " +
						 "	and t2.close > 1 " +
						 "	and ((t1.idscript = 145) or (t1.idscript = 146) or (t1.idscript = 147)) " +
						 "	order by 4 asc ";

			pstat = conn.prepareStatement(sql);
			pstat.setString(1, date);
		
			rs = pstat.executeQuery();
			while(rs.next()) {
				IndicatorVO vo = new IndicatorVO();

				double volat_min = Double.parseDouble(rs.getString("volat_min"));
				double volat_max = Double.parseDouble(rs.getString("volat_max"));
				double buy_1 = Double.parseDouble(rs.getString("buy_1"));
				double buy_2 = Double.parseDouble(rs.getString("buy_2"));
				double stop_1 = buy_1 - ((buy_1 / 100) * 5);
				double stop_2 = buy_2 - ((buy_2 / 100) * 5);
				double limit_1 = buy_1 + ((buy_1 / 100) * volat_min);
				double limit_2 = buy_2 + ((buy_2 / 100) * volat_max);
				
				vo.setIdstock(rs.getString("idstock"));
				vo.setClose(rs.getString("close"));
				vo.setVolume(rs.getString("volume"));
				vo.setMI05_perc(rs.getString("MI05_perc"));
				vo.setMI04_perc(rs.getString("MI04_perc"));
				vo.setMI05(rs.getString("MI05"));
				vo.setMI04(rs.getString("MI04"));
				vo.setVol_min(rs.getString("volat_min"));
				vo.setVol_max(rs.getString("volat_max"));
				vo.setPercHL(rs.getString("percHL"));

				vo.setBuy_1(rs.getString("buy_1"));
				vo.setStop_1(String.valueOf(stop_1));
				vo.setLimit_1(String.valueOf(limit_1));
				vo.setBuy_2(rs.getString("buy_2"));
				vo.setStop_2(String.valueOf(stop_2));
				vo.setLimit_2(String.valueOf(limit_2));
				vo.setBuy(rs.getString("buy"));
				vo.setSell(rs.getString("sell"));
				vo.setIdscript(rs.getString("idscript"));
				
				result.add(vo);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public List<IndicatorVO> listIndicator(String date) {
		List<IndicatorVO> result = new ArrayList<IndicatorVO>();
		conn = DBSession.getIstance().getConnection();
		PreparedStatement pstat = null;
		
		try {
			String sql = " select t1.idstock, t2.volume, t2.close, " +
						 "        (close / MaxMinIndex(t1.idstock, t1.date, 'MI05') * 100) - 100 as MI05_perc, " +
						 " 		  (close / MaxMinIndex(t1.idstock, t1.date, 'MI04') * 100) - 100 as MI04_perc, " +
						 "		   MaxMinIndex(t1.idstock, t1.date, 'MI05') as MI05, " +
						 "		   MaxMinIndex(t1.idstock, t1.date, 'MI04') as MI04, " +
						 "		   (SELECT MediaMinima(t1.idstock,t1.date)) as volat_min, " +
						 "		   (SELECT MediaMinima(t1.idstock,t1.date))*2 as volat_max, " +
						 "		   ((t2.high / t2.low) * 100) -100 as percHL, " +
						 "		    t2.close / ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1) as buy_1, " +
//						 "		   (t2.close / ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1)) / 1.05 as stop_1, " +
//						 "		   (t2.close / ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1)) * ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1) as limit_1, " +

						 "			t2.close / ((((SELECT MediaMinima(t1.idstock,t1.date)*4)/2)/100)+1) as buy_2, " +
//						 "			(t2.close / ((((SELECT MediaMinima(t1.idstock,t1.date)*4)/2)/100)+1)) / 1.05 as stop_2, " +
//						 "			(t2.close / ((((SELECT MediaMinima(t1.idstock,t1.date)*4)/2)/100)+1)) * ((SELECT MediaMinima(t1.idstock,t1.date)/100)+1) as limit_2, " +
						 "			 t1.buy, t1.sell, t1.idscript " +
						 "	from analyzed t1, quote t2, stocks t3 " +
						 "	where " +
						 "		t1.date = ? " +
						 "	and t1.date = t2.date " +
						 "	and t1.idstock = t2.stock " +
						 "	and t3.stock = t2.stock " +
						 "	and t3.active = 1 " +
						 "	and t2.volume > 10000 " +
						 "	and t2.close > 1 " +
						 "	and ((t1.idscript <> 145) and (t1.idscript <> 146) and (t1.idscript <> 147)) " +
						 "	order by 4 asc ";

			pstat = conn.prepareStatement(sql);
			pstat.setString(1, date);
		
			rs = pstat.executeQuery();
			while(rs.next()) {
				IndicatorVO vo = new IndicatorVO();

				double volat_min = Double.parseDouble(rs.getString("volat_min"));
				double volat_max = Double.parseDouble(rs.getString("volat_max"));
				double buy_1 = Double.parseDouble(rs.getString("buy_1"));
				double buy_2 = Double.parseDouble(rs.getString("buy_2"));
				double stop_1 = buy_1 - ((buy_1 / 100) * 5);
				double stop_2 = buy_2 - ((buy_2 / 100) * 5);
				double limit_1 = buy_1 + ((buy_1 / 100) * volat_min);
				double limit_2 = buy_2 + ((buy_2 / 100) * volat_max);
				
				vo.setIdstock(rs.getString("idstock"));
				vo.setClose(rs.getString("close"));
				vo.setVolume(rs.getString("volume"));
				vo.setMI05_perc(rs.getString("MI05_perc"));
				vo.setMI04_perc(rs.getString("MI04_perc"));
				vo.setMI05(rs.getString("MI05"));
				vo.setMI04(rs.getString("MI04"));
				vo.setVol_min(rs.getString("volat_min"));
				vo.setVol_max(rs.getString("volat_max"));
				vo.setPercHL(rs.getString("percHL"));

				vo.setBuy_1(rs.getString("buy_1"));
				vo.setStop_1(String.valueOf(stop_1));
				vo.setLimit_1(String.valueOf(limit_1));
				vo.setBuy_2(rs.getString("buy_2"));
				vo.setStop_2(String.valueOf(stop_2));
				vo.setLimit_2(String.valueOf(limit_2));

				vo.setBuy(rs.getString("buy"));
				vo.setSell(rs.getString("sell"));
				vo.setIdscript(rs.getString("idscript"));
				
				result.add(vo);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

}
